BEGIN;
    DROP DATABASE IF EXISTS bj6666;
    CREATE DATABASE bj6666 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
    USE bj6666;
    CREATE TABLE `activity` (
        `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
        `uid` integer NOT NULL,                            # 创建用户id
        `subject` varchar(64) NOT NULL DEFAULT '',         # 活动名
        `type` varchar(32) NOT NULL DEFAULT '',            # 类型
        `basecredit` integer NOT NULL DEFAULT 0,           # 基础积分，最终按一定的公式计算个人得分
        `gathertime` datetime NOT NULL,                    # 集合时间
        `startime` datetime NOT NULL,                      # 出发地点
        `gatherlocation` varchar(64) NOT NULL,             # 集合地点
        `coordx` float NOT NULL DEFAULT 0.0,               # x坐标
        `coordy` float NOT NULL DEFAULT 0.0,               # y坐标
        `accuracy` integer NOT NULL DEFAULT 0,             # 经纬度精确度
        `route` varchar(256) NOT NULL DEFAULT '',          # 路线
        `distance` integer NOT NULL DEFAULT 0,             # 距离(Km)
        `descr` varchar(512) NOT NULL DEFAULT '',          # 说明
        `ip` varchar(15) NOT NULL DEFAULT '',              # 发布者IP
        `wish` integer NOT NULL DEFAULT 0,                 # 感兴趣的人数
        `do` integer NOT NULL DEFAULT 0,                   # 参加的人数
        `dele` tinyint(1) NOT NULL DEFAULT 0,
        `atime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP        # 发布时间
    )
    ;
    CREATE TABLE `activity_threads` (
        `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
        `aid` integer NOT NULL,
        `uid` integer NOT NULL,
        `up` tinyint(1) NOT NULL DEFAULT 0,
        `dele` tinyint(1) NOT NULL DEFAULT 0,
        `subject` varchar(64) NOT NULL DEFAULT '',
        `descr` varchar(512) NOT NULL DEFAULT '',
        `ip` varchar(15) NOT NULL DEFAULT '',
        `reply` integer NOT NULL DEFAULT 0,
        `lastreply` datetime NOT NULL,
        `atime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    ;
    CREATE TABLE `activity_posts` (
        `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
        `atid` integer NOT NULL DEFAULT -1,
        `uid` integer NOT NULL DEFAULT -1,
        `descr` varchar(512) NOT NULL DEFAULT '',
        `ip` varchar(15) NOT NULL DEFAULT '',
        `dele` tinyint(1) NOT NULL DEFAULT 0,
        `atime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    ;

    CREATE TABLE `album` (
        `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
        `name` varchar(15) NOT NULL DEFAULT '',
        `uid` integer NOT NULL DEFAULT -1,
        `ip` varchar(15) NOT NULL DEFAULT '',
        `picnum` integer NOT NULL DEFAULT 0,
        `dele` tinyint(1) NOT NULL DEFAULT 0,
        `atime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    ;

    CREATE TABLE `picture` (
        `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
        `albumid` integer NOT NULL DEFAULT '-1',
        `uid` integer NOT NULL DEFAULT '-1',
        `filename` varchar(256) NOT NULL DEFAULT '',
        `size` integer NOT NULL DEFAULT 0,
        `webpath` varchar(256) NOT NULL DEFAULT '',
        `dele` tinyint(1) NOT NULL DEFAULT 0,
        `atime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    ;

    CREATE TABLE `user` (
        `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
        `email` varchar(64) NOT NULL,
        `password` varchar(32) NOT NULL,
        `nickname` varchar(16) NOT NULL,
        `location` varchar(256) NOT NULL DEFAULT '',
        `avatar` integer NOT NULL DEFAULT -1,
        `tiny_avatar` integer NOT NULL DEFAULT -1,
        `gender` integer NOT NULL DEFAULT 1,
        `age` integer NOT NULL DEFAULT 18,
        `regip` varchar(15) NOT NULL DEFAULT '',
        `regdate` datetime NOT NULL,
        `lastip` varchar(15) NOT NULL DEFAULT '',
        `lastdate` datetime NOT NULL,
        `credit` integer NOT NULL DEFAULT 0,
        `say` varchar(256) NOT NULL DEFAULT '',
        `super` tinyint(1) NOT NULL DEFAULT 0,
        `dele` tinyint(1) NOT NULL DEFAULT 0,
        `atime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    ;

    CREATE TABLE `user_say` (
        `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
        `uid` integer NOT NULL DEFAULT -1,
        `descr` varchar(128) NOT NULL DEFAULT '',          
        `dele` tinyint(1) NOT NULL DEFAULT 0,
        `atime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    ;

    CREATE TABLE `user_say_reply` (
        `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
        `uid` integer NOT NULL DEFAULT -1,
        `sid` integer NOT NULL DEFAULT -1,
        `descr` varchar(128) NOT NULL DEFAULT '',
        `dele` tinyint(1) NOT NULL DEFAULT 0,
        `atime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    ;

    CREATE TABLE `relation` (
        `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
        `uid` integer NOT NULL,
        `aid` integer NOT NULL,
        `relation` tinyint(1) NOT NULL, /* 0: wish 1: do */
        `dele` tinyint(1) NOT NULL DEFAULT 0,
        `atime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
    )
    ;

    CREATE TABLE `news` (
        `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
        `type` tinyint(16) NOT NULL DEFAULT -1, /* news type */
        `uid` integer NOT NULL DEFAULT -1, /* user  */
        `aid` integer NOT NULL DEFAULT -1, /* activity */
        `rid` integer NOT NULL DEFAULT -1, /* relation */
        `tid` integer NOT NULL DEFAULT -1, /* thread */
        `pid` integer NOT NULL DEFAULT -1, /* post */
        `ava` integer NOT NULL DEFAULT -1, /* avatar */
        `sid` integer NOT NULL DEFAULT -1, /* user say */
        `pcid` integer NOT NULL DEFAULT -1, /* post picture */
        `dele` tinyint(1) NOT NULL DEFAULT 0,
        `atime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    ;

    CREATE TABLE `feedback` (
        `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
        `repid` integer NOT NULL DEFAULT -1,
        `username` varchar(16) NOT NULL DEFAULT '',
        `descr` varchar(512) NOT NULL DEFAULT '',
        `reply` varchar(512) NOT NULL DEFAULT '',
        `ip` varchar(15) NOT NULL DEFAULT '',
        `dele` tinyint(1) NOT NULL DEFAULT 0,
        `atime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    ;

    CREATE TABLE `session` (
        `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
        `session_id` varchar(128) UNIQUE NOT NULL,
        `data` text NOT NULL,
        `atime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )   
    ;   
    COMMIT;
